import pymysql as sql
from flask import jsonify
import datetime
import userMgmt.userLink.userRouter as userRouter

connect = sql.Connect(
    host='rm-bp131vjnd9b99vmuq2o.mysql.rds.aliyuncs.com',
    port=3306,
    user='lin_432718',
    passwd='Lin817818',
    db='rail_traffic',
    charset='utf8'
)
cursor = connect.cursor()

# 返回所有的检测记录
def selectRec(risk_id):
    sql = "select risk_record_id, norm, result, time, staff_id " \
          "FROM risk_record WHERE risk_id = '" + risk_id + "'"
    # print(sql)

    cursor.execute(sql)

    attribute = ['record_id', 'record_norm', 'record_result', 'record_time', 'record_staff_id']
    l = []
    for item in cursor.fetchall():
        dic = dict(map(lambda x, y: [x, y], attribute, item))
        dic['record_time'] = dic['record_time'].strftime("%Y-%m-%d")
        l.append(dic)
    # print(l)
    return jsonify(l)

# 生成记录保存到数据库中
def addToData(risk_id, norm):

    time = datetime.datetime.now().strftime('%Y-%m-%d')
    id = getnewnumber()
    result = judge(risk_id, norm)

    sql = "INSERT INTO risk_record(risk_record_id, time, risk_id, result, staff_id, norm) VALUES " \
          "('" + id + "', '" + time + "', '" + risk_id + "', '" + result + "', '" + userRouter.admin + "', '" + norm + "')"
    print(sql)

    if result != '正常':
        cope_id = getnewnumber_cope()
        sql_1 = "INSERT INTO risk_cope(risk_cope_id, time, risk_id, status) VALUES " \
        "('" + cope_id + "', '" + time + "', '" + risk_id + "', " + "0)"
        print(sql_1)
        cursor.execute(sql_1)
        connect.commit()


    try:
        cursor.execute(sql)
        connect.commit()
    except:
        return jsonify(0)
    else:
        return jsonify(1)

# 生成一个最大编号
def getnewnumber():
    sql = "select max(risk_record_id) from risk_record"
    cursor.execute(sql)
    data = cursor.fetchone()
    # print(data)
    number = int(data[0]) + 1
    #  print(number)
    str = "%03d" % number
    # print(str)

    return str

# 生成一个处置最大编号
def getnewnumber_cope():
    sql = "select max(risk_cope_id) from risk_cope"
    cursor.execute(sql)
    data = cursor.fetchone()
    # print(data)
    number = int(data[0]) + 1
    #  print(number)
    str = "%03d" % number
    # print(str)

    return str

# 判断监测结果

def judge(risk_id, norm):

    max = 0
    min = 0
    norm = eval(norm)
    sql = "SELECT norm FROM threshold WHERE risk_id = '" + risk_id + "'"
    cursor.execute(sql)
    range = cursor.fetchall()


    if len(range) == 1:
        if norm == range[0][0]:
            return "正常"
        elif abs(norm - range[0][0])/range[0][0] <= 0.1:
            return "存在风险"
        elif abs(norm - range[0][0])/range[0][0] <= 0.2:
            return "较大风险"
        else:
            return "重大风险"
    else:
        if range[0][0] > range[1][0]:
            max = range[0][0]
            min = range[1][0]
        else:
            max = range[1][0]
            min = range[0][0]
        if norm <= max and norm >= min:
            return "正常"
        elif norm > max:
            if (norm - max) / max <= 0.1:
                return "存在风险"
            elif (norm - max) / max <= 0.2:
                return "较大风险"
            else:
                return "重大风险"
        else:
            if (min - norm) / max <= 0.1:
                return "存在风险"
            elif (min - norm) / max <= 0.2:
                return "较大风险"
            else:
                return "重大风险"


